
#-----------------------------------------模拟常规业务--------------------------------------------#

# 1、修改客户密码
#张三（卡号为1010 3576 1234 5678）修改银行卡密码为123321
#李四（卡号为1010 3576 1212 1134）修改银行卡密码为123456
UPDATE `cardInfo` set `password` = '666666' WHERE cardID = '1010357612345678';
UPDATE `cardInfo` set `password` = '999999' WHERE cardID = '1010357612121134';

#2、办理银行卡挂失
#李四（卡号为1010 3576 1212 1134）因银行卡丢失，申请挂失
UPDATE `cardInfo` set `IsReportLoss` = 1 WHERE cardID = '1010357612121134';
select `cardID` as 卡号,`curID` as 货币,`savingName` as 储蓄种类,`openDate` as 开户日期,
`openMoney` as 开户金额,`balance` as 余额,`password` as 密码,`IsReportLoss` as 是否挂失,
`customerName` as 客户姓名
from `cardInfo`,`deposit`,`userinfo`
WHERE `cardInfo`.savingID = `deposit`.savingID and `cardInfo`.customerID = `userInfo`.customerID;

#3、统计银行总存入金额和总支出金额
select `tradeType` as 交易类型,SUM(tradeMoney) as 总金额
from tradeInfo
GROUP BY `tradeType`;

#4、查询本周开户信息
select c.cardID as 卡号,u.customerName as 姓名,c.curID as 货币,d.savingName as 存款类型,
c.openDate as 开户日期,c.openMoney as 开户金额,c.balance as 余额,c.IsReportLoss as 账户状态
from cardInfo as c 
INNER JOIN userInfo as u ON (c.customerID=u.customerID)
INNER JOIN deposit as d ON (c.savingID=d.savingID)
WHERE WEEK(NOW()) = WEEK(openDate);

#5、查询本月交易金额最高的卡号
select DISTINCT cardID from `tradeInfo` where tradeMoney = (
	select MAX(tradeMoney) from `tradeInfo` 
	where MONTH(tradeDate) = MONTH(NOW()) and YEAR(tradeDate) = YEAR(NOW())
)

#6、查询挂失客户
select customerName as 客户姓名,telephone as 联系电话 from `userInfo`
where customerID IN (
	select customerID from `cardInfo` where IsReportLoss = 1
);

#7、催款提醒业务
select u.customerName as 客户姓名,u.telephone as 联系电话,c.balance as 存款金额
from `userInfo` as u INNER JOIN `cardInfo` as c ON u.customerID = c.customerID
where balance < 200;


#------------------------------------创建、使用客户友好信息视图---------------------------------#

#view_userInfo:输出银行客户记录
DROP VIEW IF EXISTS view_userInfo;
CREATE VIEW view_userInfo
AS
select customerID as 客户编号,customerName as 客户姓名,PID as 身份证号,telephone as 联系电话,
address as 地址 from `userInfo`;
select * from view_userInfo;

#view_cardInfo:输出银行卡记录
DROP VIEW IF EXISTS view_cardInfo;
CREATE VIEW view_cardInfo
AS
select c.cardID as 卡号,u.customerName as 客户姓名,c.cardID as 货币种类,d.savingName as 存款类型,
c.openDate as 开户日期,c.balance as 余额,c.IsReportLoss as 是否挂失
from `cardInfo` as c,`userInfo` as u,`deposit` as d
WHERE c.savingID = d.savingID and c.customerID = u.customerID;
select * from view_cardInfo;

#view_transInfo:输出银行卡的交易记录
DROP VIEW IF EXISTS view_transInfo;
CREATE VIEW view_transInfo
AS
select tradeDate as 交易日期,tradeType as 交易类型,cardID as 卡号,tradeMoney as 交易金额,remark as 备注
from `tradeInfo`;
select * from view_transInfo;


#----------------------------------------使用事务完成转账---------------------------------------#

/*-从卡号为“1010357612121134”的账户中转出300元给卡号为“1010357612345678”的账户-*/
/*即李四转账300元给张三*/

#查看mysql是否自动提交事务
show VARIABLES like '%autocommit%';

INSERT into tradeInfo (tradeType,cardID,tradeMoney) VALUES ('支出',1010357612121134,300);
INSERT into tradeInfo (tradeType,cardID,tradeMoney) VALUES ('存入',1010357612345678,300);
#关闭mysql自动提交事务
SET autocommit = off;

BEGIN;
UPDATE cardInfo set balance = balance -300 where cardID = 1010357612121134;
UPDATE cardInfo set balance = balance +300 where cardID = 1010357612345678;

#提交事务
COMMIT;
#rollback;#回滚事务

#检查测试数据是否正确
select * from cardInfo;
select * from tradeInfo;






















